/*
 * 版权所有 (C) 2015 知启蒙(ZHIQIM) 保留所有权利。[遇见知启蒙，邂逅框架梦]
 * 
 * https://zhiqim.org/project/zhiqim_framework/zhiqim_orm.htm
 *
 * Zhiqim ORM is licensed under Mulan PSL v2.
 * You can use this software according to the terms and conditions of the Mulan PSL v2.
 * You may obtain a copy of Mulan PSL v2 at:
 *          http://license.coscl.org.cn/MulanPSL2
 * THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
 * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
 * MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
 * See the Mulan PSL v2 for more details.
 */
package org.zhiqim.orm.datasource;

import java.io.IOException;
import java.io.StringReader;
import java.lang.reflect.Field;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.zhiqim.kernel.model.maps.MapSO;
import org.zhiqim.kernel.model.objects.SO;
import org.zhiqim.kernel.util.Classes;
import org.zhiqim.kernel.util.Hexs;
import org.zhiqim.kernel.util.Replaces;
import org.zhiqim.kernel.util.Sqls;
import org.zhiqim.orm.ORMException;
import org.zhiqim.orm.ORMServer;
import org.zhiqim.orm.dbo.Dbo;
import org.zhiqim.orm.dbo.DboField;
import org.zhiqim.orm.policy._Oracle;

/**
 * 数据库处理工具类
 *
 * @version v1.0.0 @author zouzhigang 2014-3-21 新建与整理
 */
public class ZStatement
{
    /**
     * 预处理语句设置参数，对SQL语句填充后返回SQL语句，用于打印日志
     * 
     * @param server        ORM服务
     * @param pstmt         预处理语句
     * @param index         索引
     * @param value         设置的值
     * @param str           SQL语句
     * @return              填充后的SQL语句
     * @throws SQLException SQL异常
     * @throws IOException  IO异常
     */
    public static String setParameter(ORMServer server, PreparedStatement pstmt, int index, Object value, String str) throws SQLException, IOException
    {
        if (value == null)
        {
            pstmt.setNull(index, Types.CHAR);//注意这里用Types.CHAR,可以保证string,int,long等值正确，其他的未测试
            str = str.replaceFirst("\\?", "null");
        }
        //基本类型
        else if (value instanceof Boolean)
        {
            pstmt.setBoolean(index, ((Boolean)value).booleanValue());
            str = str.replaceFirst("\\?", String.valueOf(value));
        }
        else if (value instanceof Byte)
        {
            pstmt.setByte(index, ((Byte)value).byteValue());
            str = str.replaceFirst("\\?", String.valueOf(value));
        }
        else if (value instanceof Short)
        {
            pstmt.setShort(index, ((Short)value).shortValue());
            str = str.replaceFirst("\\?", String.valueOf(value));
        }
        else if (value instanceof Integer)
        {
            pstmt.setInt(index, ((Integer)value).intValue());
            str = str.replaceFirst("\\?", String.valueOf(value));
        }
        else if (value instanceof Long)
        {
            pstmt.setLong(index, ((Long)value).longValue());
            str = str.replaceFirst("\\?", String.valueOf(value));
        }
        //日期格式
        else if (value instanceof Timestamp)
        {
            Timestamp time = (Timestamp)value;
            pstmt.setTimestamp(index, time);
            
            str = str.replaceFirst("\\?", "'" + Sqls.toDateTimeString(time) + "'");
        }
        else if (value instanceof Date)
        {
            Date date = (Date)value;
            pstmt.setDate(index, date);
            
            str = str.replaceFirst("\\?", "'" + Sqls.toDateString(date) + "'");
        }
        else if (value instanceof Time)
        {
            Time time = (Time)value;
            pstmt.setTime(index, time);
            
            str = str.replaceFirst("\\?", "'" + Sqls.toTimeString(time) + "'");
        }
        //字符串
        else if (value instanceof String)
        {
            String v = (String)value;
            if (v.length() <= 2000)
                pstmt.setString(index, v);
            else if (server.isOracle())
            {//oracle
                Clob clob = _Oracle.buildClob(pstmt.getConnection(), v);
                pstmt.setClob(index, clob);
            }
            else
            {//其他
                StringReader reader = new StringReader(v);
                pstmt.setCharacterStream(index, reader, v.length());
            }
            
            //SQL转义
            v = v.replaceAll("'", "''");
            if (v.indexOf("?") != -1)
            {//转义
                v = v.replaceAll("\\?", "-%6-%-3%-");
            }
            
            v = Replaces.toReplaceEscape(v);
            str = str.replaceFirst("\\?", "'"+ v +"'");
        }
        //二进制
        else if (value instanceof byte[])
        {
            byte[] v = (byte[])value;
            if (!server.isOracle())
                pstmt.setBytes(index, v);
            else
            {//oracle blob
                Blob blob = _Oracle.buildBlob(pstmt.getConnection(), v);
                pstmt.setBlob(index, blob);
            }
            
            str = str.replaceFirst("\\?", "'"+ Hexs.toHexString(v) +"'");
        }
        else
        {//其他类型
            pstmt.setObject(index, value);
            str = str.replaceFirst("\\?", String.valueOf(value));
        }
        
        return str;
    }
    
    /**
     * 预处理语句设置参数
     * 
     * @param server        ORM服务
     * @param pstmt         预处理语句
     * @param index         索引
     * @param value         设置的值
     * @throws SQLException SQL异常
     * @throws IOException  IO异常
     */
    public static void setParameter(ORMServer server, PreparedStatement pstmt, int index, Object value) throws SQLException, IOException
    {
        if (value == null)
            pstmt.setNull(index, Types.CHAR);//注意这里用Types.CHAR,可以保证string,int,long等值正确，其他的未测试
        
        //基本类型
        else if (value instanceof Boolean)
            pstmt.setBoolean(index, ((Boolean)value).booleanValue());
        else if (value instanceof Byte)
            pstmt.setByte(index, ((Byte)value).byteValue());
        else if (value instanceof Short)
            pstmt.setShort(index, ((Short)value).shortValue());
        else if (value instanceof Integer)
            pstmt.setInt(index, ((Integer)value).intValue());
        else if (value instanceof Long)
            pstmt.setLong(index, ((Long)value).longValue());
        //日期类型
        else if (value instanceof Timestamp)
            pstmt.setTimestamp(index, (Timestamp)value);
        else if (value instanceof Date)
            pstmt.setDate(index, (Date)value);
        else if (value instanceof Time)
            pstmt.setTime(index, (Time)value);
        //字符串
        else if (value instanceof String)
        {
            String v = (String)value;
            if (v.length() <= 2000)
                pstmt.setString(index, (String)value);
            else if (server.isOracle())
            {//oracle clob
                Clob clob = _Oracle.buildClob(pstmt.getConnection(), v);
                pstmt.setClob(index, clob);
            }
            else
            {//其他
                StringReader reader = new StringReader(v);
                pstmt.setCharacterStream(index, reader, v.length());
            }
        }
        //二进制
        else if (value instanceof byte[])
        {
            byte[] v = (byte[])value;
            if (!server.isOracle())
                pstmt.setBytes(index, v);
            else
            {//oracle blob
                Blob blob = _Oracle.buildBlob(pstmt.getConnection(), v);
                pstmt.setBlob(index, blob);
            }
        }
        else
        {//其他类型
            pstmt.setObject(index, value);
        }
    }
    
    
    /**
     * 替换SQL中##参数值为?
     * 
     * @param sql SQL语句
     * @param paramKeyList 参数KEY列表
     * @return 新的SQL语句
     */
    public static String paramMapString(String sql, List<String> paramKeyList)
    {
        for (int i=0,start=0,end=sql.indexOf('#');end!=-1;i++,start=end,end=sql.indexOf('#', start+1))
        {
            if (i % 2 == 0)
                continue;
            String paramKey = sql.substring(start + 1, end);
            paramKeyList.add(paramKey);
        }

        for (int i=0;i<paramKeyList.size();i++)
        {
            String paramKey = paramKeyList.get(i);
            sql = sql.replaceAll("#"+paramKey+"#", "?");//把#paramKey#换成?
        }
        
        return sql;
    }
    
    /**
     * 替换SQL中##参数值为结果值
     * 
     * @param sql       SQL语句
     * @param paramMap  参数KEY列表
     * @return          新的SQL语句
     */
    public static String paramMapString(String sql, MapSO paramMap)
    {
        List<String> paramKeyList = new ArrayList<String>();
        for (int i=0,start=0,end=sql.indexOf('#');end!=-1;i++,start=end,end=sql.indexOf('#', start+1))
        {
            if (i % 2 == 0)
                continue;
            
            String paramKey = sql.substring(start + 1, end);
            paramKeyList.add(paramKey);
        }

        for (int i=0;i<paramKeyList.size();i++)
        {
            String paramKey = paramKeyList.get(i);
            Object paramValue = paramMap.get(paramKey);
            sql = sql.replaceAll("#"+paramKey+"#", "'"+paramValue+"'");//把#paramKey#换成'paramValue'
        }
        
        return sql;
    }
    
    /**
     * 把SQL中的##参数转化成列表
     * 
     * @param sql               SQL语句
     * @param param             参数对象
     * @param paramDbo          参数DBO
     * @return                  参数列表
     * @throws ORMException     异常
     */
    public static List<SO> paramList(String sql, Object param, Dbo paramDbo) throws ORMException
    {
     // 获取通配符##
        List<SO> paramList = new ArrayList<>();
        for (int i = 0, start = 0, end = sql.indexOf('#'); end != -1; i++, start = end, end = sql.indexOf('#', start + 1))
        {
            if (i % 2 == 0)
                continue;
            
            String paramKey = sql.substring(start + 1, end);// 取出KEY,支持类field和表column
            DboField paramField = paramDbo.getField(paramKey);
            if (paramField == null)
                throw new ORMException("_Table/_View/_Dbo[" + paramDbo.getClass().getName() + "]没有找到[" + paramKey + "]对应的属性配置]");
    
            // 反射机制取值
            Field field = Classes.getField(param.getClass(), paramField.getField());
            if (field == null)
                throw new ORMException("_Table/_View/_Dbo[" + paramDbo.getClass().getName() + "]没有找到[" + paramKey + "]对应的属性定义]");
            
            try
            {
                Object paramValue = Classes.getFieldValue(param, field);
                paramList.add(new SO(paramKey, paramValue));
            }
            catch (Exception e)
            {
                throw new ORMException(e);
            }
        }
        
        return paramList;
    }
}
